package com.project.ds.sqlprovider;

import org.apache.ibatis.annotations.Param;

import com.project.entity.activity.Activity;

/**
 * 活动实体类Mybatis SQL语句工厂类
 * @author chenlz
 *
 */
public class ActivityMapperProvider extends BaseMapperProvider {
	
	/**
	 * 生成Select的字段累加
	 * @author chen.liangzhen
	 * @time 2017年3月24日 下午12:11:39
	 * @version v0.1.0
	 * @param userDto
	 * @return
	 */
	public String generateSelectColumnsName(Activity activity) {
		String selectColumnsName = " SELECT o.* ";
		try {
			selectColumnsName = "SELECT " + activity.returnSelectColumnsName() + " ";
		} catch (Exception e) {}
		return selectColumnsName;
	}

	/**
	 * 获取不带分页的数据
	 * @param capitalBookDto
	 * @return
	 */
	public String queryAllList(Activity activity) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT " + generateSelectColumnsName(activity) + " FROM B_ACTIVITY o ")
			.append(" ORDER BY ID DESC ");
		return sql.toString();
	}

	/**
	 * 获取带分页的数据
	 * @param activity
	 * @param pageSize
	 * @param pageNo
	 * @return
	 */
	public String queryList(Activity activity, int pageSize, int pageNo) {
		StringBuilder sql = new StringBuilder();
		sql.append(queryListBaseSql(activity))
			.append(" ORDER BY ID DESC ")
			.append(generatePageSql(pageSize, pageNo));
		return sql.toString();
	}
	
	/**
	 * 获取带分页的数据
	 * @param resourceOrderVo
	 * @param pageSize
	 * @param pageNo
	 * @return
	 */
	public String queryPageData(Activity activity, int pageSize, int pageNo) {
		StringBuilder sql = new StringBuilder();
		sql.append(findAllBaseSql(activity))
			.append(" ORDER BY ID DESC ")
			.append(generatePageSql(pageSize, pageNo));
		return sql.toString();
	}
	
	/**
	 * 查询总条数
	 * @param activity
	 * @return
	 */
	public String queryCount(Activity activity) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM ( ")
			.append(queryListBaseSql(activity))
			.append(" ) t");
		return sql.toString();
	}
	
	/**
	 * 获取带分页的基础SQL
	 * @return
	 */
	public String queryListBaseSql(Activity activity) {
		StringBuffer sql = new StringBuffer();
		sql.append(generateSelectColumnsName(activity) + "  FROM B_ACTIVITY o ");
		
		sql.append(" WHERE 1=1 ")
			.append(queryListFilterSql(activity));
		return sql.toString();
	}
	
	/**
	 * 拼装过滤条件
	 * 
	 * @param shipOrderDto
	 * @return
	 */
	public String queryListFilterSql(Activity activity) {
		StringBuilder sql = new StringBuilder();
		return sql.toString();
	}
	
	/**
	 * 生成基础SQL
		单表查询
			select t.* from test t
		多表查询
			select t.*, t2.name from test t left join test2 t2 on t.id = t2.tId
	 * @param activity
	 * @return
	 */
	public String generateBaseSql(Activity activity) {
		StringBuilder sql = new StringBuilder();
		sql.append(generateSelectColumnsName(activity))
			.append(" ,CASE WHEN CURDATE() < date_format(o.registration_start_time,'%Y-%m-%d') THEN '预热中' WHEN CURDATE() BETWEEN date_format(o.registration_start_time,'%Y-%m-%d') AND date_format(o.registration_end_time,'%Y-%m-%d') THEN '报名中' ELSE '已截止' END `status` ")
			.append(" FROM B_ACTIVITY o ");
		return sql.toString();
	}
	
	/**
	 * 组装过滤条件SQL
	 * @param activity
	 * @return
	 */
	public String generateFilterSql(Activity activity) {
		if(null == activity) {
			return "";
		}
		StringBuilder sql = new StringBuilder();
		sql.append(" AND o.publish_status = 'P'");
		if(null != activity.getId()) {
			sql.append(" AND o.id = " + activity.getId());				
		}
		
		return sql.toString();
	}
	
	/**
	 * 查询所有的数据的基础SQL
		基础SQL + where + and
	 * @return
	 */
	public String findAllBaseSql(Activity activity) {
		StringBuilder sql = new StringBuilder();
		sql.append(generateBaseSql(activity))
			.append(generateWhereSql())
			.append(generateFilterSql(activity));
		return sql.toString();
	}
	
	/**
	 * 查询所有数据的总数
	 * @param activity
	 * @return
	 */
	public String searchCount(Activity activity) {
		StringBuilder sql = new StringBuilder("SELECT COUNT(1) from ( ");
		sql.append(generateBaseSql(activity))
			.append(" )t");
		return sql.toString();
	}
	
	/**
	 * 根据ID查询
		基础SQL + where t.id = 1
	 * @param id
	 * @return
	 */
	public String findById(@Param("id")  long id) {
		StringBuilder sql = new StringBuilder();
		sql.append(generateBaseSql(new Activity()))
			.append(" WHERE o.id = {id}");
		return sql.toString();
	}
	
	/**
	 * 活动详情
	 * @author chen.liangzhen
	 * @time 2017年4月5日 下午4:45:18
	 * @version
	 * @param activityId
	 * @return
	 */
	public String queryById(@Param("activityId")Long activityId, @Param("userId")Long userId) {
		StringBuilder sql = new StringBuilder();
		sql.append(generateSelectColumnsName(new Activity()))
			.append(" ,CASE WHEN NOW() > date_format(o.activity_end_date,'%Y-%m-%d %H:%i') THEN '已过期' WHEN CURDATE() < date_format(o.registration_start_time,'%Y-%m-%d') THEN '预热中' WHEN CURDATE() BETWEEN date_format(o.registration_start_time,'%Y-%m-%d') AND date_format(o.registration_end_time,'%Y-%m-%d') THEN '报名中' ELSE '已截止' END `status` ")
			//.append(" ,(select case when count(1) > 0 then '已参加' else '未参加' end isjoin from b_activity_user a where a.user_id = #{userId} and a.activity_id = o.id) isJoin ")
			//.append(" ,(SELECT CASE WHEN au.audit_status = 0 THEN '待审核' WHEN au.cancel_time IS NOT NULL THEN '已取消' WHEN au.audit_status = 1 THEN '已通过' ELSE '未通过' END audit_status ")
			.append(" ,(SELECT CASE WHEN UPPER(au.audit_status) = 'W' THEN '待审核' WHEN au.cancel_time IS NOT NULL THEN '已取消' WHEN UPPER(au.audit_status) = 'P' THEN '已通过' ELSE '未通过' END audit_status ")
			.append(" FROM b_activity_user au WHERE au.user_id = #{userId} and au.activity_id = o.id) isJoin ")
			.append(" FROM B_ACTIVITY o ")
			.append(" WHERE id = " + activityId);
		return sql.toString();
	}
	
	/**
     * 活动详情
     * @author chen.liangzhen
     * @time 2017年4月5日 下午4:45:18
     * @version
     * @param activityId
     * @return
     */
    public String findActivityById(@Param("activityId")Long activityId) {
        StringBuilder sql = new StringBuilder();
        sql.append(generateSelectColumnsName(new Activity()))
            .append(" ,CASE WHEN NOW() > date_format(o.activity_end_date,'%Y-%m-%d %H:%i') THEN '已过期' WHEN CURDATE() < date_format(o.registration_start_time,'%Y-%m-%d') THEN '预热中' WHEN CURDATE() BETWEEN date_format(o.registration_start_time,'%Y-%m-%d') AND date_format(o.registration_end_time,'%Y-%m-%d') THEN '报名中' ELSE '已截止' END `status` ")
            .append(" FROM B_ACTIVITY o ")
            .append(" WHERE id = " + activityId);
        return sql.toString();
    }
}
